#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import mysql.connector
from mysql.connector import Error
import json
import datetime
import logging
import random
import string

# Configure logging
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger(__name__)

# Database connection configuration
DB_CONFIG = {
    'host': '45.205.31.46',
    'port': 3306,
    'user': 'root',
    'password': 'jpyvLZIX6549!',
    'database': 'test_tools',
    'charset': 'utf8mb4',
    'collation': 'utf8mb4_unicode_ci',
    'use_unicode': True
}

def get_connection():
    """
    Create and return a connection to the MySQL database.
    
    Returns:
        connection: MySQL connection object or None if connection fails
    """
    try:
        connection = mysql.connector.connect(**DB_CONFIG)
        if connection.is_connected():
            # Set the connection to use utf8mb4
            cursor = connection.cursor()
            cursor.execute('SET NAMES utf8mb4')
            cursor.execute('SET CHARACTER SET utf8mb4')
            cursor.execute('SET character_set_connection=utf8mb4')
            cursor.close()
            
            logger.info(f"Connected to MySQL database: {DB_CONFIG['database']}")
            return connection
    except Error as e:
        logger.error(f"Error connecting to MySQL: {e}")
        return None

def test_connection():
    """
    Test the database connection and return status.
    
    Returns:
        bool: True if connection is successful, False otherwise
    """
    connection = get_connection()
    if connection:
        connection.close()
        return True
    return False

def create_test_case(case_data):
    """
    Insert a new test case into the database.
    
    Args:
        case_data (dict): A dictionary containing the test case data with the following keys:
            - case_id: Unique identifier for the test case
            - title: Title of the test case
            - description: Detailed description of the test case
            - content: Complete test case content in JSON format
            - test_type: Type of test (1=functional, 2=api, 3=ui, 4=performance, 5=security, 6=other)
            - status: Status of the test case (1=draft, 2=active, 3=archived)
            - priority: Priority of the test case (0=P0, 1=P1, 2=P2, 3=P3)
            - tags: Comma-separated tags for categorization
            - created_by: Creator of the test case
            
    Returns:
        tuple: (success, message, case_id)
            - success (bool): True if insertion is successful, False otherwise
            - message (str): Success or error message
            - case_id (str): The case_id of the inserted test case if successful
    """
    required_fields = ['case_id', 'title', 'content', 'test_type']
    for field in required_fields:
        if field not in case_data or not case_data[field]:
            return False, f"Missing required field: {field}", None
    
    # Ensure content is in JSON format if it's a string
    if isinstance(case_data['content'], str):
        try:
            json.loads(case_data['content'])
        except json.JSONDecodeError:
            return False, "Content field must be valid JSON", None
    else:
        # If it's a dict, convert to JSON string with UTF-8 encoding
        case_data['content'] = json.dumps(case_data['content'], ensure_ascii=False)
    
    connection = get_connection()
    if not connection:
        return False, "Database connection failed", None
    
    try:
        cursor = connection.cursor(dictionary=True)
        
        # Check if case_id already exists
        cursor.execute("SELECT COUNT(*) as count FROM test_cases WHERE case_id = %s", (case_data['case_id'],))
        result = cursor.fetchone()
        
        if result and result['count'] > 0:
            # Generate a new unique case_id by appending a timestamp
            timestamp = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
            random_suffix = ''.join(random.choices(string.ascii_uppercase + string.digits, k=4))
            original_case_id = case_data['case_id']
            case_data['case_id'] = f"{original_case_id}_{timestamp}_{random_suffix}"
            logger.warning(f"Case ID '{original_case_id}' already exists. Generated new ID: {case_data['case_id']}")
        
        # Create query with only the fields that are present in case_data
        fields = []
        values = []
        placeholders = []
        
        # Map the case_data keys to database columns
        for key, value in case_data.items():
            if key in ['case_id', 'title', 'description', 'content', 'test_type', 
                      'status', 'priority', 'tags', 'created_by', 'last_run_time', 
                      'last_run_result']:
                fields.append(key)
                values.append(value)
                placeholders.append('%s')
        
        query = f"""
        INSERT INTO test_cases ({', '.join(fields)})
        VALUES ({', '.join(placeholders)})
        """
        
        cursor.execute(query, values)
        connection.commit()
        logger.info(f"Test case {case_data['case_id']} successfully inserted")
        return True, "Test case successfully created", case_data['case_id']
    except Error as e:
        logger.error(f"Error inserting test case: {e}")
        return False, f"Database error: {str(e)}", None
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

def get_test_case(case_id):
    """
    Retrieve a test case from the database by its case_id.
    
    Args:
        case_id (str): The unique identifier of the test case
        
    Returns:
        tuple: (success, result)
            - success (bool): True if retrieval is successful, False otherwise
            - result (dict or str): The test case data as a dictionary if successful, error message otherwise
    """
    query = "SELECT * FROM test_cases WHERE case_id = %s"
    
    connection = get_connection()
    if not connection:
        return False, "Database connection failed"
    
    try:
        cursor = connection.cursor(dictionary=True)
        cursor.execute(query, (case_id,))
        result = cursor.fetchone()
        
        if result:
            # Parse JSON content
            if 'content' in result and result['content']:
                result['content'] = json.loads(result['content'])
            return True, result
        else:
            return False, f"Test case with case_id {case_id} not found"
    except Error as e:
        logger.error(f"Error retrieving test case: {e}")
        return False, f"Database error: {str(e)}"
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

def update_test_case(case_id, update_data):
    """
    Update an existing test case in the database.
    
    Args:
        case_id (str): The unique identifier of the test case to update
        update_data (dict): Dictionary containing the fields to update
        
    Returns:
        tuple: (success, message)
            - success (bool): True if update is successful, False otherwise
            - message (str): Success or error message
    """
    if not update_data:
        return False, "No update data provided"
    
    # Ensure content is in JSON format if present and is a string
    if 'content' in update_data:
        if isinstance(update_data['content'], str):
            try:
                json.loads(update_data['content'])
            except json.JSONDecodeError:
                return False, "Content field must be valid JSON"
        else:
            # If it's a dict, convert to JSON string with UTF-8 encoding
            update_data['content'] = json.dumps(update_data['content'], ensure_ascii=False)
    
    # Build the SET part of the query
    set_clause = []
    values = []
    
    for key, value in update_data.items():
        if key in ['title', 'description', 'content', 'test_type', 
                  'status', 'priority', 'tags', 'created_by', 'last_run_time', 
                  'last_run_result']:
            set_clause.append(f"{key} = %s")
            values.append(value)
    
    if not set_clause:
        return False, "No valid fields to update"
    
    # Add case_id to values
    values.append(case_id)
    
    query = f"""
    UPDATE test_cases 
    SET {', '.join(set_clause)}
    WHERE case_id = %s
    """
    
    connection = get_connection()
    if not connection:
        return False, "Database connection failed"
    
    try:
        cursor = connection.cursor()
        cursor.execute(query, values)
        connection.commit()
        
        if cursor.rowcount > 0:
            logger.info(f"Test case {case_id} successfully updated")
            return True, "Test case successfully updated"
        else:
            return False, f"Test case with case_id {case_id} not found or no changes made"
    except Error as e:
        logger.error(f"Error updating test case: {e}")
        return False, f"Database error: {str(e)}"
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

def delete_test_case(case_id):
    """
    Delete a test case from the database.
    
    Args:
        case_id (str): The unique identifier of the test case to delete
        
    Returns:
        tuple: (success, message)
            - success (bool): True if deletion is successful, False otherwise
            - message (str): Success or error message
    """
    query = "DELETE FROM test_cases WHERE case_id = %s"
    
    connection = get_connection()
    if not connection:
        return False, "Database connection failed"
    
    try:
        cursor = connection.cursor()
        cursor.execute(query, (case_id,))
        connection.commit()
        
        if cursor.rowcount > 0:
            logger.info(f"Test case {case_id} successfully deleted")
            return True, "Test case successfully deleted"
        else:
            return False, f"Test case with case_id {case_id} not found"
    except Error as e:
        logger.error(f"Error deleting test case: {e}")
        return False, f"Database error: {str(e)}"
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

def search_test_cases(filters=None, limit=100, offset=0):
    """
    Search for test cases based on the provided filters.
    
    Args:
        filters (dict): Dictionary of filters to apply, e.g., {'test_type': 1, 'status': 2}
        limit (int): Maximum number of results to return
        offset (int): Number of results to skip (for pagination)
        
    Returns:
        tuple: (success, result)
            - success (bool): True if search is successful, False otherwise
            - result (list or str): List of test cases if successful, error message otherwise
    """
    query = "SELECT * FROM test_cases"
    params = []
    
    # Apply filters if provided
    if filters:
        conditions = []
        for key, value in filters.items():
            if key in ['case_id', 'title', 'test_type', 'status', 'priority', 'tags', 'created_by']:
                if key in ['title', 'tags', 'created_by', 'case_id'] and isinstance(value, str):
                    conditions.append(f"{key} LIKE %s")
                    params.append(f"%{value}%")
                else:
                    conditions.append(f"{key} = %s")
                    params.append(value)
        
        if conditions:
            query += " WHERE " + " AND ".join(conditions)
    
    # Add limit and offset
    query += " ORDER BY create_time DESC LIMIT %s OFFSET %s"
    params.extend([limit, offset])
    
    connection = get_connection()
    if not connection:
        return False, "Database connection failed"
    
    try:
        cursor = connection.cursor(dictionary=True)
        cursor.execute(query, params)
        results = cursor.fetchall()
        
        # Parse JSON content for each result
        for result in results:
            if 'content' in result and result['content']:
                try:
                    result['content'] = json.loads(result['content'])
                except json.JSONDecodeError:
                    result['content'] = {"error": "Invalid JSON content"}
        
        return True, results
    except Error as e:
        logger.error(f"Error searching test cases: {e}")
        return False, f"Database error: {str(e)}"
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

def check_database_encoding():
    """
    Check the character set and collation of the database and test_cases table.
    
    Returns:
        dict: Information about the database and table encoding
    """
    connection = get_connection()
    if not connection:
        return {"error": "Database connection failed"}
    
    try:
        cursor = connection.cursor(dictionary=True)
        
        # Check database character set and collation
        cursor.execute(f"SELECT default_character_set_name, default_collation_name FROM information_schema.SCHEMATA WHERE schema_name = '{DB_CONFIG['database']}'")
        db_result = cursor.fetchone() or {}
        
        # Check table character set and collation
        cursor.execute(f"SELECT table_collation FROM information_schema.TABLES WHERE table_schema = '{DB_CONFIG['database']}' AND table_name = 'test_cases'")
        table_result = cursor.fetchone() or {}
        
        # Check column character sets
        cursor.execute(f"SHOW FULL COLUMNS FROM test_cases")
        column_results = cursor.fetchall()
        
        # Check if the table has any content
        cursor.execute("SELECT COUNT(*) as count FROM test_cases")
        count_result = cursor.fetchone() or {"count": 0}
        
        # Try to insert and retrieve a test string with Chinese characters
        test_id = f"TEST_ENCODING_{datetime.datetime.now().strftime('%Y%m%d%H%M%S')}"
        try:
            # Insert a test row with Chinese characters
            cursor.execute(
                "INSERT INTO test_cases (case_id, title, description, content, test_type) VALUES (%s, %s, %s, %s, %s)",
                (test_id, "测试中文标题", "测试中文描述", json.dumps({"test": "测试内容"}, ensure_ascii=False), 1)
            )
            connection.commit()
            
            # Retrieve the test row
            cursor.execute("SELECT * FROM test_cases WHERE case_id = %s", (test_id,))
            test_result = cursor.fetchone() or {}
            
            # Delete the test row
            cursor.execute("DELETE FROM test_cases WHERE case_id = %s", (test_id,))
            connection.commit()
            
            encoding_test = {
                "original_title": "测试中文标题",
                "retrieved_title": test_result.get('title', 'N/A'),
                "success": test_result.get('title') == "测试中文标题"
            }
        except Exception as e:
            encoding_test = {"error": str(e)}
        
        return {
            "database": {
                "name": DB_CONFIG['database'],
                "character_set": db_result.get('default_character_set_name', 'N/A'),
                "collation": db_result.get('default_collation_name', 'N/A')
            },
            "table": {
                "name": "test_cases",
                "collation": table_result.get('table_collation', 'N/A')
            },
            "columns": column_results,
            "record_count": count_result.get('count', 0),
            "encoding_test": encoding_test
        }
    except Error as e:
        logger.error(f"Error checking database encoding: {e}")
        return {"error": str(e)}
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

# Example usage:
if __name__ == "__main__":
    # Test the connection
    if test_connection():
        print("Database connection successful!")
    else:
        print("Failed to connect to the database.")
        exit(1)
        
    # Example: Create a test case
    test_case = {
        "case_id": "TC_FUNC_001",
        "title": "Login Functionality Test",
        "description": "Verify that users can log in with valid credentials",
        "content": json.dumps({
            "preconditions": "User account exists in the system",
            "steps": [
                "Navigate to login page",
                "Enter valid username",
                "Enter valid password",
                "Click login button"
            ],
            "expected_results": "User is logged in and redirected to the dashboard"
        }),
        "test_type": 1,  # Functional test
        "status": 1,     # Draft
        "priority": 1,   # P1
        "tags": "login,authentication,smoke",
        "created_by": "automated_system"
    }
    
    # Uncomment to test
    # success, message, case_id = create_test_case(test_case)
    # print(f"Create test case: {success}, {message}, {case_id}")
    
    # Example: Retrieve a test case
    # success, result = get_test_case("TC_FUNC_001")
    # if success:
    #     print(f"Retrieved test case: {result['title']}")
    # else:
    #     print(f"Error: {result}") 